USE SAS
GO

IF EXISTS (SELECT 1 from sysobjects where xtype = 'FN' AND NAME = 'GetResellersXml')
	DROP FUNCTION dbo.GetResellersXml
GO


CREATE FUNCTION dbo.GetResellersXml ()
RETURNS VARCHAR(MAX)
AS
BEGIN
	
	declare 
		@xml			varchar(max)

	SET @xml		= '<RESELLERACCOUNTS><entry id="0" refsrcid="0"></entry>'

	SELECT @xml = @xml + '<entry id="' + CAST(PartnerNo AS VARCHAR) + '" refsrcid="'+ CAST( ISNULL(DATAORIGIN,-1) AS VARCHAR)+'"><![CDATA['+ PartnerName +']]></entry>' 
	FROM TR_PARTNER as p
	WHERE PartnerCategory = 2 AND PartnerType = 5 AND isActive = 1
	ORDER BY [PartnerName]
	set @xml = @xml + '</RESELLERACCOUNTS>'	

	return @xml
END


-- SELECT dbo.GetResellersXml()